﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;
using System.Data.OleDb;
using System.Runtime.InteropServices;
using System.Globalization;

using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.Taxonomy;

namespace OrgStructure_DLL
{
    public class OrgStructure
    {

        #region Members
        public string SiteStr = @"http://localhost/";
        private SPJobDefinition Job = null;
        #endregion

        #region Common
        static public string GetAppSetting(String mName)
        {
            String vStr = null;
            SPWebService mSer = SPWebService.ContentService;
            #region Ver 1
            if (mSer != null)
            {
                foreach (SPWebConfigModification mMod in mSer.WebConfigModifications)
                {
                    if (mMod.Owner == mName)
                        vStr = mMod.Value;
                }
            }
            if (vStr != null)
            {
                vStr =
                    vStr.Substring(
                        vStr.IndexOf("value=") + 7
                        , vStr.Length - 7 - vStr.IndexOf("value=") - 4);
            }
            #endregion Ver 1
            else
            #region Ver 2
            {
                string strPath =
    @"C:\inetpub\wwwroot\wss\VirtualDirectories\80\web.config";
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load(strPath);
                XmlNode node = xmlDoc.SelectSingleNode(@"/configuration/appSettings");
                foreach (XmlNode keynode in node.ChildNodes)
                {
                    string keyStr = keynode.Attributes["key"].Value;
                    if (keyStr == mName)
                    {
                        vStr = keynode.Attributes["value"].Value;
                        break;
                    }
                }
            }
            #endregion Ver 2
            if (String.IsNullOrEmpty(vStr))
                vStr = "";
            return vStr;
        }
        public void CreateLineLog(String mStr)
        {
            /*
            SPSite site = new SPSite("http://localhost");
            SPWeb mWeb = site.OpenWeb("/");
            try
            {
                SPList list = mWeb.Lists["log"];
                mWeb.AllowUnsafeUpdates = true;
                SPListItem item = list.Items.Add();
                item["Title"] = "(" + DateTime.Now.ToString() + ") " + mStr;
                item.Update();
            }
            catch
            {
            }
            mWeb.Dispose();
            site.Dispose();
            */
        }
        #endregion

        #region Exec
        public Exception Execute(Guid contentDbId, ref SPJobDefinition ParJob, string parSiteStr)
        {
            Exception ret = null;
            SiteStr = parSiteStr;
            Job = ParJob;
            Exception err = null;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                 err = InvalidateStructure(SiteStr);
            });

            return ret;
        }
        #endregion Exec

        #region DB
        public static OleDbConnection CreateSQLConnect()
        {
            OleDbConnection sqlConnection1 = new OleDbConnection(); // SqlDbConnection
            sqlConnection1.ConnectionString = GetAppSetting(@"1CConnect");
            sqlConnection1.Open();
            return sqlConnection1;
        }
        #endregion

        /// <summary>
        /// Удаляем ветку "АУП" в банке терминов раздела "Структура Организации"
        /// </summary>
        /// <param name="parSiteStr"></param>
        /// <returns></returns>
        public static Exception DeleteOrgStructure(string parSiteStr, string parNode = @"АУП")
        {
            Exception ret = null;
            try
            {
                using (SPSite site = new SPSite(parSiteStr))
                {
                    TaxonomySession mSess = new TaxonomySession(site);
                    foreach (TermStore mStore in mSess.TermStores)
                    {
                        foreach (Group mGroup in mStore.Groups)
                            if (mGroup.Name == @"Структура Организации")
                                foreach (TermSet mSet in mGroup.TermSets)
                                    if (mSet.Name == parNode)
                                        mSet.Delete();

                        mStore.CommitAll();
                    }
                }
            }
            catch (Exception err)
            {
                ret = new Exception(err.Message, err);
            }
            return ret;
        }

        public static Exception InvalidateStructure(string parSiteStr)
        {
            Exception ret = null;
            string SiteStr = parSiteStr;
            //ret = DeleteOrgStructure(SiteStr);
            //if (ret != null)
            //    return ret;

            Group yGroup = null;
            TermStore yStore = null;
            using (SPSite site = new SPSite(SiteStr))
            {

                #region Get Store Ptr
                TaxonomySession mSess = new TaxonomySession(site);
                foreach (TermStore mStore in mSess.TermStores)
                {
                    foreach (Group mGroup in mStore.Groups)
                    {
                        if (mGroup.Name == "Структура Организации")
                        {
                            yStore = mStore;
                            yGroup = mGroup;
                        }
                    }
                }
                if (yStore == null)
                    return new Exception("yStore == null");
                if (yGroup == null)
                    return new Exception("yGroup == null");
                #endregion

                try
                {
                    using (OleDbConnection conn = new OleDbConnection(GetAppSetting(@"1CConnect")))
                    {
                        conn.Open();
                        if (yGroup != null)
                        {
                            using (OleDbCommand cmd = new OleDbCommand())
                            {
                                /*
                                запрос Возвращает корень структуры организации из 1С
                                Корневые узлы "АУП", "Магазины"
                                */
                                cmd.CommandText =
    @"SELECT sp._Description as Name,sp._Code as ID,sp1._Code as ParentID 
FROM _Reference67 as sp 
LEFT JOIN _Reference67 as sp1 ON sp1._IDRRef=sp._ParentIDRRef 
WHERE sp._Code<>'09' AND sp1._Code Is Null";
                                cmd.CommandType = CommandType.Text;
                                cmd.Connection = conn;
                                using (OleDbDataReader reader = cmd.ExecuteReader())
                                {
                                    while (reader.Read())
                                    {
                                        TermSet mSet = null;
                                        string NodeName = reader["Name"].ToString().Trim();
                                        //if (NodeName != "АУП")
                                        //    continue;

                                        bool bFind = false;
                                        ret = FindTermSet(ref bFind, ref mSet, ref yGroup, NodeName);
                                        if (ret != null)
                                        {
                                            yStore.RollbackAll();
                                            reader.Close();
                                            conn.Close();
                                            return ret;
                                        }
                                        if (bFind == false)
                                        {
                                            mSet = yGroup.CreateTermSet(NodeName);
                                            mSet.Description = reader["ID"].ToString().Trim();
                                        }
                                        ret = CreateTreeOrg(ref mSet, reader["ID"].ToString());
                                        if (ret != null)
                                        {
                                            yStore.RollbackAll();
                                            reader.Close();
                                            conn.Close();
                                            return ret;
                                        }

                                        //CreateLineLog(reader["Name"].ToString());
                                    }
                                    reader.Close();
                                }
                                yStore.CommitAll();
                                conn.Close();
                            }
                        }
                    }
                }
                catch(Exception err)
                {
                    ret = new Exception(err.Message, err);
                }
            }
            

            return ret;
        } // InvalidateStructure()
        
        #region Create Tree
        // CReate Tree Level 1
        public static Exception CreateTreeOrg(ref TermSet MSet, String mID)
        {
            Exception ret = null;
            int lcid = CultureInfo.CurrentCulture.LCID;
            try
            {
                using (OleDbConnection mCo = new OleDbConnection(GetAppSetting(@"1CConnect")))
                {
                    mCo.Open();
                    using (OleDbCommand cmd = new OleDbCommand())
                    {
                        cmd.CommandText =
    @"SELECT sp._Description as Name, sp._Code as ID, sp1._Code as ParentID 
FROM _Reference67 as sp 
LEFT JOIN _Reference67 as sp1 ON sp1._IDRRef=sp._ParentIDRRef 
WHERE sp._Code<>'09' AND sp1._Code =" + mID;
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = mCo;
                        using (OleDbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Term mTerm = null;
                                bool bFind = false;
                                string NodeName = reader["Name"].ToString().Trim();
                                string idStr = reader["ID"].ToString().Trim();
                                ret = FindTerm(ref bFind, ref mTerm, ref MSet, NodeName);
                                if (ret != null)
                                {
                                    reader.Close();
                                    mCo.Close();
                                    throw ret;
                                }
                                if (!bFind)
                                {
                                    mTerm = MSet.CreateTerm(NodeName, lcid);
                                    mTerm.SetDescription(idStr, lcid);
                                }
                                CreateTreeOrg1(mTerm, idStr);
                            }
                            reader.Close();
                        }
                    }
                    mCo.Close();
                }
            }
            catch (Exception err)
            {
                ret = new Exception(err.Message, err);
            }
            return ret;
        }
        // Craete Tree level > 1
        public static Exception CreateTreeOrg1(Term MSet, String mID)
        {
            int lcid = CultureInfo.CurrentCulture.LCID;
            Exception ret = null;

            try
            {
                using (OleDbConnection mCo = new OleDbConnection(GetAppSetting(@"1CConnect")))
                {
                    mCo.Open();
                    using (OleDbCommand cmd = new OleDbCommand())
                    {

                        cmd.CommandText =
@"SELECT 
sp._Description as Name,sp._Code as ID,sp1._Code as ParentID 
FROM _Reference67 as sp 
LEFT JOIN _Reference67 as sp1 ON sp1._IDRRef=sp._ParentIDRRef 
WHERE sp._Code<>'09' AND sp1._Code =" + mID;

                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = mCo;
                        using (OleDbDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Term mTerm = null;
                                bool bFind = false;
                                string NodeName = reader["Name"].ToString().Trim();
                                string idStr = reader["ID"].ToString().Trim();
                                ret = FindTerm(ref bFind, ref mTerm, ref MSet, NodeName);
                                if (ret != null)
                                {
                                    reader.Close();
                                    mCo.Close();
                                    return ret;
                                }
                                if (!bFind)
                                {
                                    mTerm = MSet.CreateTerm(NodeName, lcid);
                                    mTerm.SetDescription(idStr, lcid);
                                }
                                CreateTreeOrg1(mTerm, idStr);
                            }
                            reader.Close();
                        }
                    }
                    mCo.Close();
                }
            }
            catch (Exception err)
            {
                ret = err;
            }
            return ret;
        }


        public static Exception FindTermSet(ref bool parIsFind, ref TermSet parTermPtr, ref Group parGroup, string parName)
        {
            Exception ret = null;
            int lcid = CultureInfo.CurrentCulture.LCID;

            foreach (TermSet mSet in parGroup.TermSets)
            {
                if (mSet.Name == parName)
                {
                    parIsFind = true;
                    parTermPtr = mSet;
                    return ret;
                }
                //Find(ref parIsFind, ref mSet, parName);
            }
            return ret;
        }

        public static Exception FindTerm(ref bool parIsFind, ref Term parSetDest, ref TermSet parSetSrc, string parName)
        {
            Exception ret = null;
            int lcid = CultureInfo.CurrentCulture.LCID;

            foreach (Term mTerm in parSetSrc.Terms)
            {
                if (mTerm.Name.Trim() == parName)
                {
                    parIsFind = true;
                    parSetDest = mTerm;
                    return ret;
                }
            }
            return ret;
        }

        public static Exception FindTerm(ref bool parIsFind, ref Term parSetDest, ref Term parSetSrc, string parName)
        {
            Exception ret = null;
            int lcid = CultureInfo.CurrentCulture.LCID;

            foreach (Term mTerm in parSetSrc.Terms)
            {
                if (mTerm.Name.Trim() == parName)
                {
                    parIsFind = true;
                    parSetDest = mTerm;
                    return ret;
                }
            }
            return ret;
        }

        #endregion

    }
}
